import { Router } from "express";
import { IcallbackFn, Ioracle } from "../../types";
import { decrypt } from "../../utils/privateKey";

export default (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  //獲取代辦事項列表
  router.post("/getduetowork", (req, res) => {
    const { username } = req.body;
    let sql = ` select a.docid,a.material_origina,a.material_replacea,a.material_product,a.current_flow,a.current_flow_phase,a.last_update_time,a.org_code,a.applier,a.apply_date`;
    sql += ` ,c.chinese_name`;
    sql += ` from xx_material_replace_apply a,xx_material_replace_apply_flow b,user_info_n c`;
    sql += ` where  a.docid = b.docid and  a.current_flow = b.flow_code and b.klm = '${decrypt(username)}' and a.applier = c.account and a.end_status = 'N' order by a.docid`;
    callbackFn(res, oracle.connChaos, { sql });
  });

  //流程獲取--獲取流程count
  router.post("/searchFlowCount", (req, res) => {
    let { param } = req.body;
    param = JSON.parse(param)
    let sql = ` select count(*)total from (`;
    sql += ` select docid,current_flow,current_flow_phase,applier,apply_date,end_status,last_update_by,last_update_time,org_code`;
    sql += ` from xx_material_replace_apply`;
    sql += ` where docid in ( select docid from xx_material_replace_apply_flow b where docid = b.docid and b.klm = '${decrypt(param.username)}' and b.flow_code <= current_flow)`;
    if (param.docid){
      sql += ` and docid = '${param.docid}'`
    } 
    if(param.start_time){
      sql += ` and apply_date >= to_date('${param.start_time}','yyyy-mm-dd')`;
    }
    if(param.end_time){
      sql += ` and apply_date <= to_date('${param.end_time}','yyyy-mm-dd') +1`;
    }
    if(param.end_status == "是"){
      sql += ` and end_status = 'Y'`
    }
    else if(param.end_status == "否"){
      sql += ` and end_status = 'N'`
    }
    sql += ` group by docid,current_flow,current_flow_phase,applier,apply_date,end_status,last_update_by,last_update_time,org_code)a`;
    callbackFn(res,oracle.connChaos,{sql})
  });

  //流程獲取--獲取流程list
  router.post("/searchFlowList", (req, res) => {
    let { param } = req.body;
    param = JSON.parse(param)
    let sql = `select * from (`;
    sql += ` select row_number() over(order by a.docid) as rowseq,a.* from (`;
    sql += ` select docid,current_flow,current_flow_phase,apply_date,last_update_by,last_update_time,org_code`;
    sql += ` ,case when end_status = 'Y' then '是' else '否' end as end_status`
    sql += ` ,(select chinese_name from user_info_n where account = applier)applier`
    sql += ` from xx_material_replace_apply`;
    sql += ` where docid in ( select docid from xx_material_replace_apply_flow b where docid = b.docid and b.klm = '${decrypt(param.username)}' and b.flow_code <= current_flow)`;
    if (param.docid){
      sql += ` and docid = '${param.docid}'`
    } 
    if(param.start_time){
      sql += ` and apply_date >= to_date('${param.start_time}','yyyy-mm-dd')`;
    }
    if(param.end_time){
      sql += ` and apply_date <= to_date('${param.end_time}','yyyy-mm-dd') +1`;
    }
    if(param.end_status == "是"){
      sql += ` and end_status = 'Y'`
    }
    else if(param.end_status == "否"){
      sql += ` and end_status = 'N'`
    }
    sql += ` group by docid,current_flow,current_flow_phase,applier,apply_date,end_status,last_update_by,last_update_time,org_code)a`;
    sql += ` )where rowseq between ${(param.page-1)*10+1} and ${(param.page)*10}`;

    callbackFn(res,oracle.connChaos,{sql})
  });

  //獲取流程詳情
  router.post("/searchFlowDetail",(req,res)=>{
    const {docid} = req.body
    let sql = ` select a.*,b.chinese_name from xx_material_replace_apply_flow a,user_info_n b `
    sql += ` where a.docid = '${docid}'`
    sql += ` and a.flow_code < (select distinct current_flow from xx_material_replace_apply where docid = '${docid}')`
    sql += ` and a.klm = b.account union `
    sql += ` select a.docid,a.flow_code,a.flow_phase,a.created_time,a.klm,'N' as approve,'' as comments,null as last_update,b.chinese_name `
    sql += ` from xx_material_replace_apply_flow a,user_info_n b `
    sql += ` where a.docid = '${docid}'`
    sql += ` and flow_code = (select distinct current_flow from xx_material_replace_apply where docid = '${docid}')`
    sql += ` and a.klm = b.account`

    callbackFn(res,oracle.connChaos,{sql})
  })

  //表單彙總查詢--為管理員可查看所有單據，不是管理員只能查看流程中所包含的單據
  router.post("/getDocInfo",(req,res)=>{
    let {data} = req.body
    data = JSON.parse(data)
    let sql = ` select count(*)c_count from xx_material_replace_admin where account = '${decrypt(data.username)}'`
    oracle.connChaos({sql}).then((result:any)=>{
      let sqlend=""
      if(data.query_type === "list"){
        sql = ` select * from (`
        sql += ` select row_number() over(order by a.docid) as rowseq,a.* from ( `
        sql += ` select a.*,b.chinese_name from xx_material_replace_apply a,user_info_n b`
        sqlend = ` )a) where rowseq between ${(data.page-1)*8+1} and ${(data.page)*8}`
      }
      else if(data.query_type === "count"){
        sql = ` select count(*)c_count from xx_material_replace_apply a,user_info_n b`
      }
      if(result.data[0].c_count == 1){
         sql += ` where 1 =1 and a.applier = b.account`
      }
      else{
         sql += ` where docid in ( select docid from xx_material_replace_apply_flow b where docid = b.docid and b.klm = '${decrypt(data.username)}' and b.flow_code <= current_flow) and a.applier = b.account`
      }
      if(data.docid){
        sql += ` and docid = '${data.docid}'`
      }
      if(data.material_origina){
        sql += ` and material_origina ='${data.material_origina}'`
      }
      if(data.material_replacea){
        sql += ` and material_replacea ='${data.material_replacea}'`
      }
      if(data.material_product){
        sql += ` and material_product ='${data.material_product}'`
      }
      if(data.start_time){
        sql += ` and apply_date >= to_date('${data.start_time}','yyyy-mm-dd')`
      }
      if(data.end_time){
        sql += ` and apply_date <= to_date('${data.end_time}','yyyy-mm-dd') +1`;
      }
      if(data.end_status == "是"){
        sql += ` and end_status = 'Y'`
      }
      else if(data.end_status == "否"){
        sql += ` and end_status = 'N'`
      }
      sql += sqlend
      callbackFn(res,oracle.connChaos,{sql})
    }).catch((err)=>{
      res.send({code:0,message:"數據庫異常",data:[err]})
    })
  })

  return router;
};
